﻿using NPOI.SS.UserModel;
using System;
using System.Diagnostics;
using System.IO;
using OfficeOpenXml;
using NPOI.XWPF.UserModel;
using Magicodes.ExporterAndImporter.Excel;

namespace ExcelDemo
{
    public class Program
    {
        static readonly string fileName = Path.Combine(Directory.GetCurrentDirectory(), @"excel\批量导入学员模板文件.xlsx");

        public static void Main(string[] args)
        {
            Console.WriteLine("excel导入演示: ");
            Console.WriteLine("  1. Npoi");
            Console.WriteLine("  2. EPPlus");
            Console.WriteLine("  3. Magicodes.IE");
            Console.WriteLine("  4. 计算字段测试");
            Console.WriteLine("请输入选择项: 1-4");

            var key = Console.ReadKey();
            Console.WriteLine(Environment.NewLine);

            var st = new Stopwatch();
            st.Start();
            switch ((int)char.GetNumericValue(key.KeyChar))
            {
                case 1:
                    Console.WriteLine("Npoi: ");
                    NpoiDemo();
                    break;
                case 2:
                    Console.WriteLine("EPPlus: ");
                    EPPlusDemo();
                    break;
                case 3:
                    Console.WriteLine("Magicodes.IE: ");
                    MagicodesIE();
                    break;
                case 4:
                    Console.WriteLine("计算字段测试: ");
                    CalcExcel();
                    break;
                default:
                    Console.WriteLine("退出演示.");
                    break;
            }

            st.Stop();
            Console.WriteLine($"处理时间: {st.ElapsedMilliseconds}");
            Console.ReadKey();
        }

        private static void MagicodesIE()
        {
            var Importer = new ExcelImporter();
            var result = Importer.Import<ImportDto>(fileName).Result;
            if (!result.HasError)
            {
                foreach (var item in result.Data)
                {
                    Console.WriteLine($"手机号: {item.Phone}");
                    Console.WriteLine($"姓名: {item.Name}");
                    Console.WriteLine($"所属企业: {item.Company}");
                    Console.WriteLine($"证书编号: {item.Code}");
                    Console.WriteLine($"发证日期: {item.PostDate?.ToString("yyyy年MM月dd")}");
                    Console.WriteLine(Environment.NewLine);
                }
            }

        }

        public static void NpoiDemo()
        {
            var excel = WorkbookFactory.Create(fileName);
            var sheet = excel.GetSheetAt(0);

            var rowCount = sheet.LastRowNum;
            // 从excel文件第2行开始读取数据
            for (int i = 1; i <= rowCount; i++)
            {
                var row = sheet.GetRow(i);  //读取当前行数据
                if (row != null)
                {
                    Console.WriteLine($"id: {i}");
                    Console.WriteLine($"手机号: {row.GetCell(0)?.ToString()}");
                    Console.WriteLine($"姓名: {row.GetCell(1)?.ToString()}");
                    Console.WriteLine($"所属企业: {row.GetCell(2)?.ToString()}");
                    Console.WriteLine($"证书编号: {row.GetCell(3)?.ToString()}");
                    Console.WriteLine($"发证日期: {row.GetCell(4)?.DateCellValue.ToString("yyyy年MM月dd")}");
                    Console.WriteLine(Environment.NewLine);
                }
            }
        }

        public static void EPPlusDemo()
        {
            var file = new FileInfo(fileName);
            //ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using ExcelPackage package = new ExcelPackage(file);
            var worksheet = package.Workbook.Worksheets[0];

            var rowCount = worksheet.Dimension.Rows;
            for (int i = 2; i <= rowCount; i++)
            {
                Console.WriteLine($"id: {i - 1}");
                Console.WriteLine($"手机号: {worksheet.Cells[i, 1]?.GetValue<string>()}");
                Console.WriteLine($"姓名: {worksheet.Cells[i, 2]?.GetValue<string>()}");
                Console.WriteLine($"所属企业: {worksheet.Cells[i, 3]?.GetValue<string>()}");
                Console.WriteLine($"证书编号: {worksheet.Cells[i, 4]?.GetValue<string>()}");
                Console.WriteLine($"发证日期: {worksheet.Cells[i, 5]?.GetValue<DateTime?>()?.ToString("yyyy年MM月dd")}");
                Console.WriteLine(Environment.NewLine);
            }
        }

        public static void CalcExcel()
        {
            using var package = new ExcelPackage();
            package.Workbook.CalcMode = ExcelCalcMode.Manual;
            var worksheet = package.Workbook.Worksheets.Add("sheet1");
            worksheet.SetValue("A1", 2);
            worksheet.SetValue("B1", 3);
            worksheet.SetValue("C1", 3);

            var cell = worksheet.Cells["A2"];
            cell.Formula = "SUM(A1:C1)";
            package.Workbook.Calculate();
            Console.WriteLine($"A1+B1={cell.GetValue<int>()}");
        }
    }
}
